IF OBJECT_ID('dbo.RptFinancialAgingDetailReport') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.RptFinancialAgingDetailReport
    IF OBJECT_ID('dbo.RptFinancialAgingDetailReport') IS NOT NULL
        PRINT '<<< FAILED DROPPING PROCEDURE dbo.RptFinancialAgingDetailReport >>>'
    ELSE
        PRINT '<<< DROPPED PROCEDURE dbo.RptFinancialAgingDetailReport >>>'
END
go
-- =======================================================================
-- Procedure Name: dbo.RptFinancialAgingDetailReport
--
-- Author:      Ryan McCarthy
--
-- Create date: 05/07/2009
--
-- Description: Gets data for Financial Aging Report
--
-- Parameters:  
--     @StartDate                         
--     @EndDate                          
--     @Institution                       
--     @Department                        
--     @Unit        
--     @FundNumber
--     @PrincipalInvestigator 
--     @Sponsor        
--     @OriginatingSponsor                    
--     @Subcontractor                     
--     @RMManager        
--     @DeliverableType           
--     @DeliverableStatus 
--     @GroupBy          
--     @GroupType			 
--     @SelectedHeaderStatus		             
--     @DomainUserId                      
--
--
-- Returns:     INT, 0=Success, -1=Error
--
-- SampleCall:
-- RptFinancialAgingDetailReport NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'ORG',120,NULL,11055 
-- EXECUTE dbo.RptFinancialAgingDetailReport(
--                       		@StartDate                      = NULL,   
--     					@EndDate                        = NULL,  
--     					@Institution                    = NULL,   
--     					@Department                     = NULL,   
--     					@Unit        			= NULL,
--     					@FundNumber			= NULL,
--    				     	@PrincipalInvestigator 		= NULL,
--     					@Sponsor        		= NULL,
--     					@OriginatingSponsor             = NULL,       
--     					@Subcontractor                  = NULL,   
--     					@RMManager	        	= NULL,
--     					@DeliverableType           	= NULL,
--     					@DeliverableStatus 		= NULL,
--     					@GroupBy   		   	= 'ORG',          
--					@GroupType			= 120, 
--					@SelectedHeaderStatus		= NULL,
--     					@DomainUserId              	= 11055 )
--
--------------------------------------------------------------------------
-- Date        Initials  Modification
--------------------------------------------------------------------------
-- 05/07/09    RM        Created based on RptSubcontractDeliverableDetailReport.sql
--
-- =======================================================================
CREATE PROCEDURE dbo.RptFinancialAgingDetailReport
     (
	
     @StartDate                         DATETIME = NULL,  
     @EndDate                           DATETIME = NULL,
     @Institution                       VARCHAR(100) = NULL,
     @Department                        VARCHAR(100) = NULL,
     @Unit                              VARCHAR(100) = NULL,
     @FundNumber                        VARCHAR(1000) = NULL,
     @PrincipalInvestigator             VARCHAR(100) = NULL, 
     @Sponsor                           VARCHAR(100) = NULL,
     @OriginatingSponsor                VARCHAR(100) = NULL,
     @Subcontractor                     VARCHAR(200) = NULL,
     @RMStaffMember	             	VARCHAR(100) = NULL,
     @DeliverableType                   VARCHAR(200) = NULL,
     @DeliverableStatus                 VARCHAR(5000) = NULL,
     @GroupBy                           VARCHAR(20) = NULL, 
     @GroupType				INT = NULL, --30,60,90,120 or NULL for All
     @SelectedHeaderStatus		VARCHAR(1000) = NULL,
     @DomainUserId                      INT

     ) 
     
WITH RECOMPILE                                          
AS
BEGIN
     -- Initial Settings
     SET NOCOUNT ON


     -- Declare Local Variables
     DECLARE   @Error              INT,
               @RowCount           INT, 
	       @DeliverableStatusCodes VARCHAR(5000)

-- ALL checking
     IF isNull(@DeliverableStatus,'All') IN ('ALL','','0') SET @DeliverableStatus = 'All'
     IF @Institution IN ('ALL','','0') SET @Institution = NULL
     IF @Department IN ('ALL','','0') SET @Department = NULL
     IF @Unit IN ('ALL','','0') SET @Unit = NULL
     IF @FundNumber IN ('ALL','','0') SET @FundNumber = NULL
     IF @PrincipalInvestigator IN ('ALL','','0') SET @PrincipalInvestigator = NULL
     IF @Sponsor IN ('ALL','','0') SET @Sponsor = NULL
     IF @OriginatingSponsor IN ('ALL','','0') SET @OriginatingSponsor = NULL
     IF @Subcontractor IN ('ALL','','0') SET @Subcontractor = NULL
     IF @RMStaffMember IN ('ALL','','0') SET @RMStaffMember = NULL
     IF @GroupType IS NULL SET @GroupType = NULL

     --Verify @GroupType 
     IF @GroupType IS NOT NULL AND @GroupType NOT IN (30,60,90,120)
     BEGIN
          RAISERROR ('ERROR! GroupType has an invalid value. Set to a valid GroupType and try again.',11,1,@GroupType) 
          RETURN -1
     END
     
  -- Set Deliverable Type
     IF @DeliverableType IS NOT NULL
          SELECT @DeliverableType = CASE @DeliverableType 
                              WHEN 'Financial' THEN 'REPORTCAT_1'
                              ELSE 'REPORTCAT_1'   -- ALL
                           END

     -- Check to see if All is anywhere in the status string
	IF @DeliverableStatus like '%,All%' OR @DeliverableStatus like '%All,%'
	BEGIN
		SELECT @DeliverableStatus = 'All'
	END

     	-- If drilling down to a single status, set the codes here
	SELECT 	  @DeliverableStatusCodes = 'ERROR'


	 If @SelectedHeaderStatus is NULL or @SelectedHeaderStatus = 'Pending'  --'REPORTSTAT_61'    
	 BEGIN
		SELECT    @DeliverableStatusCodes = CASE WHEN @DeliverableStatus LIKE '%Pending%' or @DeliverableStatus = 'All' THEN @DeliverableStatusCodes + ',REPORTSTAT_61' ELSE @DeliverableStatusCodes END
         END   

         If @SelectedHeaderStatus is NULL or @SelectedHeaderStatus = 'FSRDue'  --'REPORTSTAT_36'
	 BEGIN
		SELECT    @DeliverableStatusCodes = CASE WHEN @DeliverableStatus LIKE '%FSRDue%' or @DeliverableStatus = 'All' THEN @DeliverableStatusCodes + ',REPORTSTAT_36' ELSE @DeliverableStatusCodes END
	 END

 	 If @SelectedHeaderStatus is NULL or @SelectedHeaderStatus = 'FSRAssigned'  --'REPORTSTAT_37'
	 BEGIN
		SELECT    @DeliverableStatusCodes = CASE WHEN @DeliverableStatus LIKE '%FSRAssigned%' or @DeliverableStatus = 'All' THEN @DeliverableStatusCodes + ',REPORTSTAT_37' ELSE @DeliverableStatusCodes END
	 END

	 If @SelectedHeaderStatus is NULL or @SelectedHeaderStatus = 'FSROutToDept'  --'REPORTSTAT_38'
	 BEGIN
		SELECT    @DeliverableStatusCodes = CASE WHEN @DeliverableStatus LIKE '%FSROutToDept%' or @DeliverableStatus = 'All' THEN @DeliverableStatusCodes + ',REPORTSTAT_38' ELSE @DeliverableStatusCodes END
	 END
	 
	 If @SelectedHeaderStatus is NULL or @SelectedHeaderStatus = 'FSRCompleted'  --'REPORTSTAT_39'
	 BEGIN
		SELECT    @DeliverableStatusCodes = CASE WHEN @DeliverableStatus LIKE '%FSRCompleted%' or @DeliverableStatus = 'All' THEN @DeliverableStatusCodes + ',REPORTSTAT_39' ELSE @DeliverableStatusCodes END
	 END
		
	 If @SelectedHeaderStatus is NULL 
	 BEGIN 
		 SELECT    @DeliverableStatusCodes = CASE WHEN isNull(@DeliverableStatus,'All') = 'All' THEN 'REPORTSTAT_36,REPORTSTAT_37,REPORTSTAT_38,REPORTSTAT_39,REPORTSTAT_61' --ALL
                                            ELSE @DeliverableStatusCodes END
	 END

     --Remove error string if other values were added to string
     SELECT @DeliverableStatusCodes = replace(@DeliverableStatusCodes,'ERROR,','')
   
     --Check for Errors
     IF @DeliverableStatusCodes = 'ERROR' and isNull(@DeliverableStatus,'All') <> 'All'
     BEGIN
          RAISERROR ('ERROR! DeliverableStatus has an invalid value. Set to a valid StatusColumn name.',11,1,@DeliverableStatus) 
          RETURN -1
     END
     
     -- Performance Issues, Get Secured AgreementIds into a temp table
     CREATE TABLE   #tt_Agreements  (AgreementId INT)
     
     INSERT    #tt_Agreements (AgreementId)
     SELECT    AgreementId
     FROM      dbo.fnGetSecurityAgreements(@DomainUserId) 
     
     -- Result Set into temp table
     SELECT    AI.Institution,
	       AI.FolderNumber,
               AI.InfoEdPropNumber,
	       AI.PrincipalInvestigator,
               AI.ChiefCode,
               AI.SponsorName,
	       AI.SecondarySponsor,
               AI.SponsorAgreementID,
	       BUDG.SequenceYear as AwardPeriod,
               BUDG.BudgetStartDate as AwardStartDate,
	       BUDG.BudgetViewType as AwardPeriodStatus,
	       AI.PostAwardManagerName,
	       ISNULL(DEL.SubmittedBy,'UNASSIGNED') AS RMStaffMember,
	       NULL as NOGAUploadDate, --Need to find this in Insight
               BUDG.FundNumber as PSProjectNumber,
               AI.ProjectStartDate,
               AI.ProjectEndDate,
               AI.InstrumentType,   
	       DEL.DueDate as DeliverableDueDate, 
	       DEL.ReportName as DeliverableName,
               DEL.REPORTCAT as ReportCategory,
               DEL.REPORTSTAT as ReportStatus,
               DEL.Comments AS DeliverableComments,                                
               NULL AS SubContractorName, --Need to find this in Insight
               DEL.ReportId,
               DEL.REPORTCAT_CODE,
               DEL.REPORTSTAT_CODE,
               AI.AgreementId,
               AI.InstitutionId,
               AI.DepartmentId,
               AI.UnitId,
               AI.SponsorId,
               AI.PrincipalInvestigatorId,
               AI.SubcontractsYN,
               AI.SubContractorId
INTO #AgingReport
FROM      dbo.AgrAgreement_Info AI 
               -- Security Issues: Not all Users can see all Agreements!
               INNER JOIN
                    #tt_Agreements AUTH ON AI.AgreementId = AUTH.AgreementId
                    
               INNER JOIN
                    (SELECT D.prop_no,
                            D.ReportId,
                            D.ReportName,
                            D.REPORTCAT_CODE,
                            D.REPORTCAT,
                            D.REPORTSTAT,
                            D.REPORTSTAT_CODE,
                            D.DueDate,
                            D.SubmittedDate,
                            D.ProcessedDate AS StatusDate,
                            D.CompletedDate,
                            D.SubmittedBy,
                            D.StatusBy,
                            D.CompletedBy,
                            D.PeriodStartDate,
                            D.PeriodEndDate,
                            D.Comments,
                            D.DaysComplCalc
                    FROM    dbo.vwInfoEdFinancialDeliverables D
                            INNER JOIN (SELECT  ReportId,
                                                MAX(ProcessedDate) AS LastDate
                                        FROM    dbo.vwInfoEdFinancialDeliverables
                                        GROUP BY ReportId) LAST ON D.ReportId = LAST.ReportId AND 
                                                  D.ProcessedDate = LAST.LastDate ) DEL ON AI.InfoEdPropNumber = DEL.prop_no  
                LEFT OUTER JOIN agrAgreement_Budget BUDG ON BUDG.AgreementID = AI.AgreementID AND BudgetViewType <> 'Awarded' and SequenceYear > 1
			and SequenceYear = (SELECT MIN(SequenceYear) FROM agrAgreement_Budget WHERE BUDG.AgreementID = AgreementID AND BudgetViewType <> 'Awarded' and SequenceYear > 1)
     WHERE     	(@StartDate IS NULL OR @EndDate IS NULL OR BUDG.BudgetStartDate BETWEEN @StartDate AND @EndDate) AND
               	(@DeliverableType IS NULL OR DEL.REPORTCAT_CODE IN (SELECT Code FROM dbo.fnGetMultiCriteriaCode(@DeliverableType))) AND
               	(@Institution IS NULL OR AI.InstitutionId IN (SELECT Criteria_ID FROM dbo.fnGetMultiCriteria(@Institution))) AND
              	(@Department IS NULL OR ISNULL(AI.DepartmentId,-1) IN (SELECT Criteria_ID FROM dbo.fnGetMultiCriteria(@Department))) AND
               	(@Unit IS NULL OR AI.UnitId IN (SELECT Criteria_ID FROM dbo.fnGetMultiCriteria(@Unit))) AND
               	(@PrincipalInvestigator IS NULL OR AI.PrincipalInvestigatorId IN (SELECT Criteria_ID FROM dbo.fnGetMultiCriteria( @PrincipalInvestigator ))) AND
              	(@Sponsor IS NULL OR AI.SponsorId IN (SELECT Criteria_ID FROM dbo.fnGetMultiCriteria( @Sponsor ))) AND
 		(@GroupType IS NULL OR DEL.duedate between dateadd(dd,-@GroupType,getdate()) and dateadd(dd,30-isNULL(@GroupType,0),getdate())) AND   -- deliverable due date is within this range
		DEL.duedate > dateadd(dd,-120,getdate())  AND 
		DEL.duedate < dateadd(dd,1,getdate()) 
    ORDER BY  AI.Institution,
               AI.Department,
               AI.Unit,
               AI.FolderNumber,
               DEL.StatusDate
              

     SELECT @Error=@@ERROR,@RowCount=@@ROWCOUNT
     IF @Error!=0
     BEGIN
          RAISERROR ('ERROR! Getting Intial Agreement list into TempTable, @Proposals',11,1)
          DROP TABLE #tt_Agreements
          RETURN -1
     END
     
     -- Even on Success. Drop now.
     DROP TABLE #tt_Agreements
     
     -- Return Result Set using additional params
     Select distinct 
			Institution,
	       		FolderNumber,
			InfoEdPropNumber,
	               	PrincipalInvestigator,
               		ChiefCode,
              		SponsorName,
			SecondarySponsor,
		       	SponsorAgreementID,
	       		AwardPeriod,
               		AwardStartDate,
	       		AwardPeriodStatus,
	       		PostAwardManagerName,
	       		RMStaffMember,
	       		NOGAUploadDate, --Need to find this in Insight
               		PSProjectNumber,
               		ProjectStartDate,
               		ProjectEndDate,
               		InstrumentType,   
	       		DeliverableDueDate, 
	       		DeliverableName,
               		ReportCategory,
               		ReportStatus,
               		DeliverableComments,
			SubContractorName,
                    	ReportId,
                    	REPORTCAT_CODE,
                    	REPORTSTAT_CODE,
                    	AgreementId,
                    	InstitutionId,
                    	DepartmentId,
			UnitId,
                    	SponsorId,
                    	PrincipalInvestigatorId,
                   	SubcontractsYN,
                   	SubContractorId
     FROM #AgingReport
     WHERE     (@FundNumber IS NULL OR PSProjectNumber IN (SELECT Code FROM dbo.fnGetMultiCriteriaCode(@FundNumber))) AND
               (@OriginatingSponsor IS NULL OR SecondarySponsor = @OriginatingSponsor ) AND
               -- If user is drilling down, this would be passed...
               (@Subcontractor IS NULL OR SubContractorName = @SubContractor) AND
     	       (@RMStaffMember IS NULL OR RMStaffMember = @RMStaffMember) 
	       
     
     SELECT @Error=@@ERROR,@RowCount=@@ROWCOUNT
     IF @Error!=0
     BEGIN
          RETURN -1
     END

     -- Return Success
     RETURN 0

     
END
go

IF OBJECT_ID('dbo.RptFinancialAgingDetailReport') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE dbo.RptFinancialAgingDetailReport >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE dbo.RptFinancialAgingDetailReport >>>'
go

GRANT EXECUTE ON dbo.RptFinancialAgingDetailReport TO InsightReport,Insight
go


